﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Data;

/// <summary>
/// Summary description for Customer
/// </summary>
public class Customers
{
    public long ID = -1;

    //Ca nhan
    public string HoTen = "";//Họ và tên *
    public string BirthDay = "";//Bao nhiêu tuổi (or năm sinh) *
    public byte GioiTinh = 1;//Giới tính
    public string QueQuan = "";//Quê quán   *
    public string DiaChiThuongTru = "";//Thường trú *
    public string HomePhone = "";//Số điện thoại nhà riêng
    public string Email = "";//Email cá nhận
    public string TrinhDoHocVan = "";//Trình độ học vấn     *
    public string BangCapCaoNhat = "";//Bằng cấp cao nhất Vi tri *
    public string KinhNghiem = "";//Kinh nghiệm *
    public string ThoiGianCongTac = "";
    public string SoThich = ""; //*
    public string TheThao = "";

    //gia dinh
    public string VC_Ten = "";
    public string VC_QueQuan = "";//*
    public string VC_NgheNghiep = "";//*
    public string VC_NangKhieu = "";//*
    public string ConCai = "";//*
    public string QueBoMeRuot = "";
    public string QueBoMeVo = "";
    public string Note = "";
    public byte ORieng = 1;

    //Chi tiet khach hang
    public string TenHang = "";
    public string GiaTriHang = "";
    public string TrongLuongHang = "";
    public byte KhoHayBai = 1;
    public string SoLuongXK = "";
    public string SoLuongNK = "";
    public string PercentCNFFOB = "";
    public long PortToID = 0;
    public long PortFromID = 0;
    public long CarierID = 0;
    public string TieuChiUuTien = "";
    public string ThanhToan = "";
    public string LuongHangChiaSe = "";
    public string QuyTrinhRaQuyetDinh = "";
    public int LoaiKhachHang = 0;
    public string DoanhSoDuKien = "";
    public DateTime LastUpdate = DateTime.Now;
    public string GUID = "";

    public Customers()
    {

    }

    public long Save(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "";
        string strTableName = "tbl_Customers";


        if (ID <= 0) // new object, we insert new record to database
        {
            strSQL = "Insert into " + strTableName + " (" +
                        "`HoTen`" +
                        ",`BirthDay`" +
                        ",`GioiTinh`" +
                        ",`QueQuan`" +
                        ",`DiaChiThuongTru`" +
                        ",`HomePhone`" +
                        ",`Email`" +
                        ",`TrinhDoHocVan`" +
                        ",`BangCapCaoNhat`" +
                        ",`KinhNghiem`" +
                        ",`ThoiGianCongTac`" +
                        ",`SoThich`" +
                        ",`TheThao`" +
                        ",`VC_Ten`" +
                        ",`VC_QueQuan`" +
                        ",`VC_NgheNghiep`" +
                        ",`VC_NangKhieu`" +
                        ",`ConCai`" +
                        ",`QueBoMeRuot`" +
                        ",`QueBoMeVo`" +
                        ",`Note`" +
                        ",`ORieng`" +
                        ",`TenHang`" +
                        ",`GiaTriHang`" +
                        ",`TrongLuongHang`" +
                        ",`KhoHayBai`" +
                        ",`SoLuongNK`" +
                        ",`SoLuongXK`" +
                        ",`PercentCNFFOB`" +
                        ",`PortToID`" +
                        ",`PortFromID`" +
                        ",`CarierID`" +
                        ",`TieuChiUuTien`" +
                        ",`ThanhToan`" +
                        ",`LuongHangChiaSe`" +
                        ",`QuyTrinhRaQuyetDinh`" +
                        ",`LoaiKhachHang`" +
                        ",`DoanhSoDuKien`" +
                        ",`LastUpdate`" +
                        ",`GUID`" +
                        ") " +
                     " values(" +
                     "'" + DBModule.RefineString(HoTen) + "'" +
                     ",'" + DBModule.RefineString(BirthDay) + "'" +
                         "," + GioiTinh + "" +
                         "," + "'" + DBModule.RefineString(QueQuan) + "'" +
                         "," + "'" + DBModule.RefineString(DiaChiThuongTru) + "'" +
                         "," + "'" + DBModule.RefineString(HomePhone) + "'" +
                         "," + "'" + DBModule.RefineString(Email) + "'" +
                         "," + "'" + DBModule.RefineString(TrinhDoHocVan) + "'" +
                         "," + "'" + DBModule.RefineString(BangCapCaoNhat) + "'" +
                         "," + "'" + DBModule.RefineString(KinhNghiem) + "'" +
                         "," + "'" + DBModule.RefineString(ThoiGianCongTac) + "'" +
                         "," + "'" + DBModule.RefineString(SoThich) + "'" +
                         ",'" + DBModule.RefineString(TheThao) + "'" +
                         ",'" + DBModule.RefineString(VC_Ten) + "'" +
                         ",'" + DBModule.RefineString(VC_QueQuan) + "'" +
                         ",'" + DBModule.RefineString(VC_NgheNghiep) + "'" +
                         ",'" + DBModule.RefineString(VC_NangKhieu) + "'" +
                         ",'" + DBModule.RefineString(ConCai) + "'" +
                         ",'" + DBModule.RefineString(QueBoMeRuot) + "'" +
                         ",'" + DBModule.RefineString(QueBoMeVo) + "'" +
                         ",'" + DBModule.RefineString(Note) + "'" +
                         "," + ORieng +
                         ",'" + DBModule.RefineString(TenHang) + "'" +
                         ",'" + DBModule.RefineString(GiaTriHang.ToString()) + "'" +
                         ",'" + DBModule.RefineString(TrongLuongHang.ToString()) + "'" +
                         "," + KhoHayBai +
                         ",'" + DBModule.RefineString(SoLuongNK.ToString()) + "'" +
                         ",'" + DBModule.RefineString(SoLuongXK.ToString()) + "'" +
                         ",'" + DBModule.RefineString(PercentCNFFOB.ToString()) + "'" +
                         "," + PortToID.ToString() +
                         "," + PortFromID.ToString() +
                         "," + CarierID.ToString() +
                         ",'" + DBModule.RefineString(TieuChiUuTien) + "'" +
                         ",'" + DBModule.RefineString(ThanhToan) + "'" +
                         ",'" + DBModule.RefineString(LuongHangChiaSe.ToString()) + "'" +
                         ",'" + DBModule.RefineString(QuyTrinhRaQuyetDinh) + "'" +
                         "," + LoaiKhachHang +
                         ",'" + DBModule.RefineString(DoanhSoDuKien.ToString()) + "'" +
                         "," + DBModule.RefineDatetime(LastUpdate) +
                         ",'" + GUID + "'" +
                         ")";
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("Customers", "add", cn, trans);
            string sqlCommand = "select ID from tbl_Customers where GUID='" + GUID + "' limit 0,1;";

            DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
            }
            else
                return -1;
        }
        else
        {
            strSQL = "Update " + strTableName + " set " +
                "HoTen='" + DBModule.RefineString(HoTen) + "'" +
                ",BirthDay='" + DBModule.RefineString(BirthDay) + "'" +
                ",GioiTinh=" + GioiTinh +
                ",QueQuan='" + DBModule.RefineString(QueQuan) + "'" +
                ",DiaChiThuongTru='" + DBModule.RefineString(DiaChiThuongTru) + "'" +
                ",HomePhone='" + DBModule.RefineString(HomePhone) + "'" +
                ",Email='" + DBModule.RefineString(Email) + "'" +
                ",TrinhDoHocVan='" + DBModule.RefineString(TrinhDoHocVan) + "'" +
                ",BangCapCaoNhat='" + DBModule.RefineString(BangCapCaoNhat) + "'" +
                ",KinhNghiem='" + DBModule.RefineString(KinhNghiem) + "'" +
                ",ThoiGianCongTac='" + DBModule.RefineString(ThoiGianCongTac) + "'" +
                ",SoThich='" + DBModule.RefineString(SoThich) + "'" +
                ",TheThao='" + DBModule.RefineString(TheThao) + "'" +
                ",VC_Ten='" + DBModule.RefineString(VC_Ten) + "'" +
                ",VC_QueQuan='" + DBModule.RefineString(VC_QueQuan) + "'" +
                ",VC_NgheNghiep='" + DBModule.RefineString(VC_NgheNghiep) + "'" +
                ",VC_NangKhieu='" + DBModule.RefineString(VC_NangKhieu) + "'" +
                ",ConCai='" + DBModule.RefineString(ConCai) + "'" +
                ",QueBoMeRuot='" + DBModule.RefineString(QueBoMeRuot) + "'" +
                ",QueBoMeVo='" + DBModule.RefineString(QueBoMeVo) + "'" +
                ",Note='" + DBModule.RefineString(Note) + "'" +
                ",ORieng=" + ORieng +
                ",TenHang='" + DBModule.RefineString(TenHang) + "'" +
                ",GiaTriHang='" + GiaTriHang + "'" +
                ",TrongLuongHang='" + DBModule.RefineString(TrongLuongHang.ToString()) + "'" +
                ",KhoHayBai=" + KhoHayBai +
                ",SoLuongNK='" + DBModule.RefineString(SoLuongNK.ToString()) + "'" +
                ",SoLuongXK='" + DBModule.RefineString(SoLuongXK.ToString()) + "'" +
                ",PercentCNFFOB='" + DBModule.RefineString(PercentCNFFOB.ToString()) + "'" +
                ",PortToID=" + PortToID.ToString() +
                ",PortFromID=" + PortFromID.ToString() +
                ",CarierID=" + CarierID.ToString() +
                ",TieuChiUuTien='" + DBModule.RefineString(TieuChiUuTien) + "'" +
                ",ThanhToan='" + DBModule.RefineString(ThanhToan) + "'" +
                ",LuongHangChiaSe='" + DBModule.RefineString(LuongHangChiaSe.ToString()) + "'" +
                ",QuyTrinhRaQuyetDinh='" + DBModule.RefineString(QuyTrinhRaQuyetDinh) + "'" +
                ",LoaiKhachHang=" + LoaiKhachHang +
                ",DoanhSoDuKien='" + DBModule.RefineString(DoanhSoDuKien) + "'" +
                ",LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + " " +
                " where ID=" + ID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("Customers", "edit", cn, trans);
            return ID;
        }
    }

    public void Delete(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "DELETE FROM tbl_Customers WHERE ID = " + this.ID.ToString();
        DBModule.ExecuteNonQuery(strSQL, cn, trans);
        UserObjectsLog.SaveObjectActionLog("Customers", "delete", cn, trans);
    }

    public void Load(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "SELECT * FROM tbl_Customers WHERE ID = " + ID.ToString() + " limit 0,1";
        DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        //Fill data into this object
        if (ds.Tables[0].Rows.Count > 0)
        {
            DataRow dr = ds.Tables[0].Rows[0];
            if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
            if (!dr.IsNull("HoTen")) HoTen = dr["HoTen"].ToString();
            if (!dr.IsNull("BirthDay")) BirthDay = dr["BirthDay"].ToString();
            if (!dr.IsNull("GioiTinh")) GioiTinh = byte.Parse(dr["GioiTinh"].ToString());
            if (!dr.IsNull("QueQuan")) QueQuan = dr["QueQuan"].ToString();
            if (!dr.IsNull("DiaChiThuongTru")) DiaChiThuongTru = dr["DiaChiThuongTru"].ToString();
            if (!dr.IsNull("HomePhone")) HomePhone = dr["HomePhone"].ToString();
            if (!dr.IsNull("Email")) Email = dr["Email"].ToString();
            if (!dr.IsNull("TrinhDoHocVan")) TrinhDoHocVan = dr["TrinhDoHocVan"].ToString();
            if (!dr.IsNull("BangCapCaoNhat")) BangCapCaoNhat = dr["BangCapCaoNhat"].ToString();
            if (!dr.IsNull("KinhNghiem")) KinhNghiem = dr["KinhNghiem"].ToString();
            if (!dr.IsNull("ThoiGianCongTac")) ThoiGianCongTac = dr["ThoiGianCongTac"].ToString();
            if (!dr.IsNull("SoThich")) SoThich = dr["SoThich"].ToString();
            if (!dr.IsNull("TheThao")) TheThao = dr["TheThao"].ToString();
            if (!dr.IsNull("VC_Ten")) VC_Ten = dr["VC_Ten"].ToString();
            if (!dr.IsNull("VC_QueQuan")) VC_QueQuan = dr["VC_QueQuan"].ToString();
            if (!dr.IsNull("VC_NgheNghiep")) VC_NgheNghiep = dr["VC_NgheNghiep"].ToString();
            if (!dr.IsNull("VC_NangKhieu")) VC_NangKhieu = dr["VC_NangKhieu"].ToString();
            if (!dr.IsNull("ConCai")) ConCai = dr["ConCai"].ToString();
            if (!dr.IsNull("QueBoMeRuot")) QueBoMeRuot = dr["QueBoMeRuot"].ToString();
            if (!dr.IsNull("QueBoMeVo")) QueBoMeVo = dr["QueBoMeVo"].ToString();
            if (!dr.IsNull("Note")) Note = dr["Note"].ToString();
            if (!dr.IsNull("ORieng")) ORieng = byte.Parse(dr["ORieng"].ToString());
            if (!dr.IsNull("TenHang")) TenHang = dr["TenHang"].ToString();
            if (!dr.IsNull("GiaTriHang")) GiaTriHang = dr["GiaTriHang"].ToString();
            if (!dr.IsNull("TrongLuongHang")) TrongLuongHang = dr["TrongLuongHang"].ToString();
            if (!dr.IsNull("KhoHayBai")) KhoHayBai = byte.Parse(dr["KhoHayBai"].ToString());
            if (!dr.IsNull("SoLuongNK")) SoLuongNK = dr["SoLuongNK"].ToString();
            if (!dr.IsNull("SoLuongXK")) SoLuongXK = dr["SoLuongXK"].ToString();
            if (!dr.IsNull("PercentCNFFOB")) PercentCNFFOB = dr["PercentCNFFOB"].ToString().ToString();
            if (!dr.IsNull("PortToID")) PortToID = long.Parse(dr["PortToID"].ToString());
            if (!dr.IsNull("PortFromID")) PortFromID = long.Parse(dr["PortFromID"].ToString());
            if (!dr.IsNull("CarierID")) CarierID = long.Parse(dr["CarierID"].ToString());
            if (!dr.IsNull("TieuChiUuTien")) TieuChiUuTien = dr["TieuChiUuTien"].ToString();
            if (!dr.IsNull("ThanhToan")) ThanhToan = dr["ThanhToan"].ToString();
            if (!dr.IsNull("LuongHangChiaSe")) LuongHangChiaSe = dr["LuongHangChiaSe"].ToString();
            if (!dr.IsNull("QuyTrinhRaQuyetDinh")) QuyTrinhRaQuyetDinh = dr["QuyTrinhRaQuyetDinh"].ToString();
            if (!dr.IsNull("LoaiKhachHang")) LoaiKhachHang = int.Parse(dr["LoaiKhachHang"].ToString());
            if (!dr.IsNull("DoanhSoDuKien")) DoanhSoDuKien = dr["DoanhSoDuKien"].ToString();
            if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
        }
    }

    public static void GetList(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out int nTotalItem, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
    {
        NumberOfPages = 0;
        nTotalItem = 0;
        string strSQL = "";
        ds = null;

        // build SQL statement
        strSQL = "Select * from tbl_Customers where 1=1 ";
        if ((strCondition != null) && (strCondition != ""))
        {
            strSQL += strCondition;
        }
        if ((OrderBy != null) && (OrderBy != ""))
            strSQL += " Order By " + DBModule.RefineString(OrderBy);
        // run SQL statement
        if (NumOfItemsPerPage > 0)
        {
            ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, out nTotalItem, cn, trans);

        }
        else
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);

    }
}